﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_cm_MoveUpRotatingBanner')
BEGIN
    PRINT 'Dropping Procedure proc_cm_MoveUpRotatingBanner'
    DROP  Procedure  proc_cm_MoveUpRotatingBanner
END
GO

PRINT 'Creating Procedure proc_cm_MoveUpRotatingBanner'
GO

CREATE PROCEDURE [dbo].[proc_cm_MoveUpRotatingBanner]
	@pRotatingBannerId uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @vPriorityNum INT

	SELECT @vPriorityNum = [priority_num]
	FROM [dbo].[tbl_rotating_banner] (NOLOCK)
	WHERE [rotating_banner_id] = @pRotatingBannerId

	IF @vPriorityNum > 1
	BEGIN
		UPDATE [dbo].[tbl_rotating_banner]
		SET [priority_num] = [priority_num] + 1
		WHERE [priority_num] = @vPriorityNum - 1

		UPDATE [dbo].[tbl_rotating_banner]
		SET [priority_num] = @vPriorityNum - 1
		WHERE [rotating_banner_id] = @pRotatingBannerId
	END
END
GO

GRANT EXEC ON dbo.proc_cm_MoveUpRotatingBanner TO PUBLIC
GO



